package com.education.module.studentExamResult;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.education.domain.Certificate;
import com.education.domain.Student;
import com.education.domain.StudentExamResult;
import com.education.framework.base.BaseServices;
import com.education.framework.dao.IDao;
import com.education.framework.domain.SearchParams;
import com.education.framework.page.Page;

@Service
public class StudentExamResultServices extends BaseServices implements IDao<StudentExamResult>{
    
	
	public List<StudentExamResult> findResult(int studentId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT t.id ,t.student_id, t.certificate_id,t.pass  FROM student_exam_result t where t.student_id = ? ");
		List<Object> argsList = new ArrayList<Object>();
		argsList.add(studentId);
		Object[] args = argsList.toArray();
		List<StudentExamResult> list = dao.query(sql.toString(),args,new StudentExamResultListRowmapper());
		return list;
	}
	
	private class StudentExamResultListRowmapper implements RowMapper<StudentExamResult> {
		public StudentExamResult mapRow(ResultSet rs, int arg1) throws SQLException {
			StudentExamResult obj = new StudentExamResult();
			obj.setResultId(rs.getInt("resultId"));
			obj.setStudentId(rs.getInt("sutdent_id"));
			obj.setCertificateId(rs.getInt("certificate_id"));
			obj.setPass(rs.getInt("pass"));
			return obj;
		}
	}
	
	public StudentExamResult findStudentExamResult(int studentId,int CertId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT t.id as resultId,t.student_id, t.certificate_id,t.pass  FROM student_exam_result t where t.student_id = ? and t.certificate_id = ? ");
		List<Object> argsList = new ArrayList<Object>();
		argsList.add(studentId);
		argsList.add(CertId);
		Object[] args = argsList.toArray();
		/*int res =findRecordCount(sql.toString(),args);
		if(res<=0){
			StudentExamResult studentExamResult = new StudentExamResult();
			return studentExamResult;
		}*/
		List<StudentExamResult> list = dao.query(sql.toString(), args , new StudentExamResult1Rowmapper());
		StudentExamResult studentExamResult = new StudentExamResult();
		if(list.size() >= 1) {
			studentExamResult = list.get(0);
		}
		return studentExamResult;
	
	}
	
	private class StudentExamResult1Rowmapper implements RowMapper<StudentExamResult> {
		public StudentExamResult mapRow(ResultSet rs, int arg1) throws SQLException {
			StudentExamResult obj = new StudentExamResult();
			obj.setResultId(rs.getInt("resultId"));
			obj.setStudentId(rs.getInt("student_id"));
			obj.setCertificateId(rs.getInt("certificate_id"));
			obj.setPass(rs.getInt("pass"));
			return obj;
		}
	}
	
	
	
	
	public List<Student> findStudentHasCert(SearchParams searchParams, Page page) {
		StringBuffer sql = new StringBuffer();
		sql.append("select stu.id,stu.truename,cl.class_name,stu.class_id, case when (select count(*) from student_exam_result ss where ss.student_id =stu.id and ss.pass <>1) then '0' else '1' end as passFlag ");
		sql.append(" from student stu inner join class cl on cl.id = stu.class_id ");
		sql.append("where stu.id in ( select sr.student_id from student_exam_result sr ) ");
		String lp = " and ";
		List<Object> argsList = new ArrayList<Object>();
		if(null != searchParams){
			if(null != searchParams.get("className") && !"".equals((String)searchParams.get("className"))){
				sql.append(lp).append(" cl.class_name like ? ");
				argsList.add("%" + searchParams.get("className") + "%");
				lp = " and ";
			}
			if(null != searchParams.get("studentName") && !"".equals((String)searchParams.get("studentName"))){
				sql.append(lp).append(" stu.truename like ? ");
				argsList.add("%" + searchParams.get("trueName") + "%");
				lp = " and ";
			}
		}
		Object[] args = argsList.toArray();
		List<Student> list = dao.query(pageSQL(sql.toString(),page),args,new StudentHasCertRowmapper());
		page.setTotalItem(findRecordCount("select count(id) as cNum from student_exam_result ",new Object[]{}));
		return list;
	}
	private class StudentHasCertRowmapper implements RowMapper<Student> {
		public Student mapRow(ResultSet rs, int arg1) throws SQLException {
			Student obj = new Student();
			obj.setStudentId(rs.getInt("id"));
			obj.setTruename(rs.getString("truename"));
			obj.setClassId(rs.getInt("class_id"));
		    obj.setClassName(rs.getString("class_name"));
		    obj.setPassFlag(rs.getString("passFlag"));
			return obj;
		}
	}
	
	
	public List<Certificate> findCert() {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT id,cert_name,description,professional_id,remark FROM certificate order by certificate.id ");
		List<Certificate> list = dao.query(sql.toString(), new CertificateRowmapper());
		return list;
	}
	
	private class CertificateRowmapper implements RowMapper<Certificate> {
		public Certificate mapRow(ResultSet rs, int arg1) throws SQLException {
			Certificate obj = new Certificate();
			obj.setId(rs.getInt("id")); 
			obj.setRemark(rs.getString("remark")); 
			obj.setDescription(rs.getString("description")); 
			obj.setCertName(rs.getString("cert_name")); 
			obj.setProfessionalId(rs.getInt("professional_id")); 
			return obj;
		}
	}

	@Override
	public List<StudentExamResult> find(SearchParams searchParams, Page page) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT id,sutdent_id,certificate_id,pass FROM student_exam_result");
		String lp = " where ";
		List<Object> argsList = new ArrayList<Object>();
		if(null != searchParams){
			if(null != searchParams.get("title") && !"".equals((String)searchParams.get("title"))){
				sql.append(lp).append(" title like ? ");
				argsList.add("%" + searchParams.get("title") + "%");
				lp = " and ";
			}
		}
		Object[] args = argsList.toArray();
		List<StudentExamResult> list = dao.query(pageSQL(sql.toString(),page),args,new StudentExamResultRowmapper());
		page.setTotalItem(findRecordCount("select count(id) as cNum from student_exam_result ",new Object[]{}));
		return list;
	}
	
	public int getExist(int studentId,int certId){
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from student_exam_result t where t.student_id = ? and t.certificate_id = ? ");
		List<Object> argsList = new ArrayList<Object>();
		argsList.add(studentId);
		argsList.add(certId);
		Object[] args = argsList.toArray();
		return dao.queryForInt(sql.toString(), args);
		
	}
	public List<StudentExamResult> find() {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT id,sutdent_id,certificate_id,pass FROM student_exam_result");
		
		List<StudentExamResult> list = dao.query(sql.toString(),new StudentExamResultRowmapper());
		return list;
	}

	@Override
	public int save(StudentExamResult obj) {
		 
 StringBuffer sql = new StringBuffer(); 
 sql.append("insert into student_exam_result ( "); 
 sql.append("id,sutdent_id,certificate_id,pass ");  
 sql.append(" ) values(?,?,?,?) "); 
 Object[] args = {obj.getId(),obj.getStudentId(),obj.getCertificateId(),obj.getPass() };
		 
		 dao.update(sql.toString(), args);
		return dao.queryForInt("SELECT LAST_INSERT_ID()"); 
	}

	@Override
	public StudentExamResult findForObject(Integer id) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT id,sutdent_id,certificate_id,pass FROM student_exam_result ");
		sql.append(" where id=? ");
		
		Object[] args = {id};
		return dao.queryForObject(sql.toString(),args,new StudentExamResultRowmapper());
	}
	
	public int updateResultByOne(int  resultId, int pass,int studentId) {
		 StringBuffer sql = new StringBuffer(); 
		 sql.append("update student_exam_result t set t.pass = ? where t.id =?  "); 
		 Object[] args = {pass,resultId };
		 dao.update(sql.toString(), args);
		 
		 StringBuffer sql1 = new StringBuffer();
		 sql1.append("select count(*) from student_exam_result ss where ss.student_id = ? and ss.pass <>1  ");
		 Object[] args1 = {studentId};
		 int res = dao.queryForInt(sql1.toString(),args1);
		 return res;
	}
	
	

	@Override
	public void update(StudentExamResult obj) {
		 
		 StringBuffer sql = new StringBuffer(); 
		 sql.append("update student_exam_result "); 
		 sql.append("set  "); 
		 sql.append("sutdent_id=?,certificate_id=?,pass=? where id=?  "); 
		 Object[] args = {obj.getStudentId(),obj.getCertificateId(),obj.getPass(),obj.getId() };
				 
		 dao.update(sql.toString(), args);
	}

	@Override
	public void delete(Integer id) {
		String sql = "delete from student_exam_result where id=?";
		dao.update(sql, new Object[]{id});
	}
	public void deleteByStudentId(Integer studentId) {
		String sql = "delete from student_exam_result  where student_id = ? ";
		dao.update(sql, new Object[]{studentId});
	}

	@Override
	public boolean findIsExist(String name) {
		return false;
	}
	
	private class StudentExamResultRowmapper implements RowMapper<StudentExamResult> {
		@Override
		public StudentExamResult mapRow(ResultSet rs, int arg1) throws SQLException {
			StudentExamResult obj = new StudentExamResult();
			obj.setId(rs.getInt("id")); 
			obj.setCertificateId(rs.getInt("certificate_id")); 
			obj.setStudentId(rs.getInt("sutdent_id")); 
			obj.setPass(rs.getInt("pass")); 

			return obj;
		}
	}

}
